Google Sheets Query: How to Filter by Date Range

#Google Sheets Query: How to Filter by Date Range| 来源: 网络整理| 查看: 265

You can use the following formula to filter by a date range in a Google Sheets query:

=QUERY(A1:C9,"select * where A > date '"&TEXT(DATEVALUE("1/1/2020"),"yyyy-mm-dd")&"'")

This particular formula returns all rows in the range A1:C9 where the date is after 1/1/2020.

The following examples show how to use this formula in practice with the following dataset:

Example 1: Filter Rows Before Some Date

We can use the following formula to filter for rows where the date is before 1/10/2020:

=QUERY(A1:C17,"select * where A < date '"&TEXT(DATEVALUE("1/10/2020"),"yyyy-mm-dd")&"'")

Notice that only the rows where the date is before 1/10/2020 are returned.

Example 2: Filter Rows After Some Date

We can use the following formula to filter for rows where the date is after 1/10/2020:

=QUERY(A1:C17,"select * where A > date '"&TEXT(DATEVALUE("1/10/2020"),"yyyy-mm-dd")&"'")

Notice that only the rows where the date is after 1/10/2020 are returned.

Example 3: Filter Rows Between Two Dates

We can use the following formula to filter for rows where the date is between 1/5/2020 and 1/15/2020:

=QUERY(A1:C17,"select * where A > date '"&TEXT(DATEVALUE("1/5/2020"),"yyyy-mm-dd")&"'and A < date '"&TEXT(DATEVALUE("1/15/2020"),"yyyy-mm-dd")&"'")

Notice that only the rows where the date is between 1/5/2020 and 1/15/2020 are returned.

Additional Resources

The following tutorials explain how to perform other common operations with dates in Google Sheets:

How to AutoFill Dates in Google Sheets How to Add & Subtract Days in Google Sheets How to Calculate the Difference Between Two Dates in Google Sheets

【本文地址】

公司简介

联系我们